Fortunately, you can activate the data design tools of Visual Studio 2010 from any sort of project (UIbased or otherwise) without the need to copy-and-paste massive amounts of code between projects. You can see this in action by adding more functionality to AutoLot.dll.
This time, try copying the entire AutoLot (Version two) project folder you created earlier in this chapter to a new location on your hard drive and rename this folder to AutoLot (Version Three). Next, activate the Visual Studio 2010 File > Open Project/Solution... menu option, and open the AutoLotDAL.sln file in your new AutoLot (Version Three) folder.
Now, insert a new strongly typed DataSet class (named AutoLotDataSet.xsd) into your new project using the Project > Add New Item menu option (see Figure 22-24).
Figure 22-24
This opens a blank Dataset Designer surface. At this point, you can use the Server Explorer to connect to a given database (you should already have a connection to AutoLot), and drag-and-drop each table and stored procedure you wish to generate onto the surface. In Figure 22-25, you can see each of the custom aspects of AutoLot are accounted for, and their relationships are realized automatically (this example does not drag over the CreditRisk table).
Figure 22-25 Your custom strongly typed types, this time within a Class Library project
The DataSet designer created the exact same sort of code that the DataGridView wizard did in the previous Windows Forms example. However, this time you account for the Inventory, Customers, and Orders tables, as well as the GetPetName stored procedure, so you have many more generated classes. Basically, each database table you drag onto the designer surface gives you a strongly typed DataSet, DataTable, DataRow, and data adapter class.
The strongly typed DataSet, DataTable, and DataRow classes will be placed into the root namespace of the project (AutoLot). The custom table adapters will be placed within a nested namespace. You can view all of the generated types most easily by using the Class View tool, which you open from the Visual Studio View menu (see Figure 22-26).
Figure 22-26 The autogenerated strongly typed data of the AutoLot database
For the sake of completion, you might want use the Visual Studio 2010 Properties editor (see Chapter 14 for details) to set the version of this latest incarnation of AutoLot.dll to 3.0.0.0.
Source Code You can find the AutoLotDAL (Version 3) project under the Chapter 22 subdirectory.
At this point, you can use your strongly typed data within any .NET application that needs to communicate with the AutoLot database. To make sure you understand the basic mechanics, create a Console application named StronglyTypedDataSetConsoleClient. Next, add a reference to your latestand- greatest version of AutoLot.dll and import the AutoLotDAL and the AutoLotDAL.AutoLotDataSetTableAdapters namespace into your initial C# code file.
Here is a Main() method that uses the InventoryTableAdapter object to retrieve all data in the Inventory table. Notice that you do not need to specify a connection string because that information is now part of the strongly typed object model. Once you fill the table, you print out the results using a helper method named PrintInventory(). Note that you can manipulate the strongly typed DataTable just as you do a “normal” DataTable using the Rows and Columns collections:
{
static void Main(string[] args)
{
Console.WriteLine("***** Fun with Strongly Typed DataSets *****\n");
AutoLotDataSet.InventoryDataTable table =
new AutoLotDataSet.InventoryDataTable();
InventoryTableAdapter dAdapt = new InventoryTableAdapter();
dAdapt.Fill(table);
PrintInventory(table);
Console.ReadLine();
}
static void PrintInventory(AutoLotDataSet.InventoryDataTable dt)
{
// Print out the column names.
for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
{
Console.Write(dt.Columns[curCol].ColumnName + "\t");
}
Console.WriteLine("\n----------------------------------");
// Print the data.
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
{
Console.Write(dt.Rows[curRow][curCol].ToString() + "\t");
}
Console.WriteLine();
}
}
}
Now assume you want to insert new records using this strongly typed object model. The following helper function adds two new rows to the current InventoryDataTable, then updates the database using the data adapter. You add the first row manually by configuring a strongly typed DataRow; you add the second row by passing in the needed column data, which allows the DataRow to be created in the background automatically:
public static void AddRecords(AutoLotDataSet.InventoryDataTable tb, InventoryTableAdapter dAdapt) { // Get a new strongly typed row from the table. AutoLotDataSet.InventoryRow newRow = tb.NewInventoryRow(); // Fill row with some sample data. newRow.CarID = 999; newRow.Color = "Purple"; newRow.Make = "BMW"; newRow.PetName = "Saku"; // Insert the new row. tb.AddInventoryRow(newRow); // Add one more row, using overloaded Add method. tb.AddInventoryRow(888, "Yugo", "Green", "Zippy"); // Update database. dAdapt.Update(tb); }
The Main() method can invoke this method; doing so updates the database table with these new records:
static void Main(string[] args) { ... // Add rows, update and reprint. AddRecords(table, dAdapt); table.Clear(); dAdapt.Fill(table); PrintInventory(table); Console.ReadLine(); }
Deleting records with this strongly typed object model is also simple. The autogenerated FindByXXXX() method (where XXXX is the name of the primary key column) of the strongly typed DataTable returns the correct (strongly typed) DataRow using the primary key. Here is another helper method that deletes the two records you just created:
private static void RemoveRecords(AutoLotDataSet.InventoryDataTable tb, InventoryTableAdapter dAdapt) { AutoLotDataSet.InventoryRow rowToDelete = tb.FindByCarID(999); dAdapt.Delete(rowToDelete.CarID, rowToDelete.Make, rowToDelete.Color, rowToDelete.PetName); rowToDelete = tb.FindByCarID(888); dAdapt.Delete(rowToDelete.CarID, rowToDelete.Make, rowToDelete.Color, rowToDelete.PetName); }
If you call this from your Main() method and reprint the table, you should find these two test records are no longer displayed.
Note If you run this app twice and call the AddRecord() method each time, you get a VIOLATION CONSTRAINT ERROR because the AddRecord() method tries to insert the same CarID primary key value each time. If you wish to make this example more flexible, you can gather data from the user.
Let’s look at one more example of using the strongly typed object model. In this case, you create a final method that invokes the GetPetName stored procedure. When the data adapters for the AutoLot database were created, there was a special class created named QueriesTableAdapter, which as the name implies, encapsulates the process of invoking stored procedures in the relational database. This final helper function displays the name of the specified car when called from Main():
public static void CallStoredProc() { QueriesTableAdapter q = new QueriesTableAdapter(); Console.Write("Enter ID of car to look up: "); string carID = Console.ReadLine(); string carName = ""; q.GetPetName(int.Parse(carID), ref carName); Console.WriteLine("CarID {0} has the name of {1}", carID, carName); }
At this point, you know how to use strongly typed database types, and package them up into a dedicated class library. You can find more aspects of this object model to play around with, but you should be in a perfect position to dig deeper if that interests you. To wrap things up for this chapter, you will learn how to apply LINQ queries to an ADO.NET DataSet object.
Source Code You can find the StronglyTypedDataSetConsoleClient project under the Chapter 22 subdirectory.